数据库定义语言

Data Definition Languages.

数据库

创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name
    [[DEFAULT] CHARACTER SET [=] charset_name ]
   	[[DEFAULT] COLLATE [=] collation_name ];

可选项为数据库使用的字符集和校对规则，不声明则使用默认值。创建数据库前应该指定所使用的字符集，否则数据表中容易出现乱码。

在MySQL中：SCHEMA和DATABASE是同义词。在PostgreSQL中，SCHEMA是命名空间，将一个数据库划分为逻辑空间。

修改数据库

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET [=] charset_name ]
    [[DEFAULT] COLLATE [=] collation_name ];

主要是修改数据的字符集和校对方法。如果db_name没有指定，则是对默认的数据库进行修改。

删除数据库

DROP DATABASE [IF EXISTS] database_name;

表

创建表格

CREATE TABLE [IF NOT EXISTS] table_name (
  column_name data_type [column_contraint], ...
)
#[table_options]
#[partition_options]
#[IGNORE | REPLACE];

column_definition表示表的列的声明，每个声明包含：

col_name data_type[(params)]
  [NOT NULL | NULL]
  [DEFAULT value] 
  [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

column_constraint表示对数值的一个或多个约束，例如NOT NULL、UNIQUE、PRIMARY KEY、AUTO_INCREMENT、比较规则（COLLATE）等；

table_options表示设置表的一些属性，例如：引擎（ENGINE）、字符集（CHARACTER SET）、全局比较规则（COLLATE）等，以及约束条件等。

MySQL :: MySQL 8.0 Reference Manual :: 13.1.20 CREATE TABLE Statement

PostgreSQL: Documentation: 12: CREATE TABLE

PostgreSQL Schema。

注释

COMMENT ON TABLE "employees" IS '雇员信息';
COMMENT ON COLUMN "employees"."department" IS '部门名称';

根据其他表格创建

基于源表的数据类型重新创建数据表并插入数据（WITH NO DATA不插入数据）。

CREATE TABLE dup_table AS (SELECT * FROM table_name);
CREATE TABLE dup_table AS (SELECT * FROM table_name) WITH NO DATA;

上述方法不会保留源数据表字段的属性。可以使用以下方法复制源表的属性，然后再从源表复制数据。

CREATE TABLE dup_table (LIKE table_name INCLUDING ALL);

查看表的定义

查询数据表的属性：

SELECT table_catalog as db,
       TABLE_SCHEMA as schema, 
       TABLE_NAME, TABLE_TYPE, [VERSION, ]
       TABLE_ROWS as num_rows,
       CREATE_TIME 
   FROM information_schema.tables where table_name='table_name';

table_schema：MySQL为数据库名，PostgreSQL为模式名（table_catalog为数据库名）；

MySQL查询方法。

PostgreSQL查询方法。

索引

PRIMARY KEY：

Foreign Keys：所定义列取值范围由所引用的外部数据表列确定。

column_name data_type references another_table(col_name)

创建索引

CREATE [UNIQUE] INDEX [[IF NOT EXISTS] index_name] ON table_name [USING method]
    ( {column_name | (expression) }[ASC|DESC][, ...] ) -- index columns
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ] -- partial index

USING method: PostgreSQL provides the index methods btree (default), hash, gist, spgist, gin, and brin.

{column_name|(expression): Multiple fields can be specified if the index method supports multicolumn indexes. An index field can be an expression computed from the values of one or more columns of the table row. This feature can be used to obtain fast access to data based on some transformation of the basic data. For example, an index computed on upper(col) would allow the clause WHERE upper(col) = 'JIM' to use an index.

UNIQUE: 索引列数据有重复值将产生错误。

删除表

DROP TABLE table_name;

修改表

修改表的字段类型

ALTER TABLE table_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

默认不改变列的位置。

修改表的字段名称和类型

ALTER TABLE table_name CHANGE [COLUMN] 
	old_col_name new_col_name column_definition 
	[FIRST|AFTER col_name]

只修改名称则列的定义应该保持不变，只修改列的定义则old_col_name和 new_col_name应该相同。可以选择更改列的位置。

增加表的字段

ALTER TABLE table_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]

FIRST:表示作为表的首个字段； AFTER col_name：表示位于col_name字段之后；默认：增加字段位于表的末端

删除表的字段

ALTER TABLE table_name DROP [COLUMN] col_name

更改表名

ALTER TABLE table_name RENAME [TO] new_tbl_name

注意：CHANGE/FIRST/AFTER/COLUMN这些关键字是MySQL在标准SQL上的扩展，在其他数据库上不一定适用。

https://www.yiibai.com/sql/sql-create-table.html

Partitioning

Bucketing

CLUSTERED BY声明分组。

CREATE TABLE table_name (column1 data_type, column2 data_type, …) PARTITIONED BY (partition1 data_type, partition2 data_type,….) CLUSTERED BY (clus_col1) SORTED BY (sort_col2) INTO n BUCKETS;

分组被存储为文件。

Learning Programming Book